package ua.com.fesvlast.hotel.model.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import ua.com.fesvlast.hotel.model.connectionpool.ConnectionManager;
import ua.com.fesvlast.hotel.model.enteties.Contacts;
import ua.com.fesvlast.hotel.model.enteties.HotelRooms;
import ua.com.fesvlast.hotel.model.enteties.Orders;

public class OrdersDAO {
	Connection conn =ConnectionManager.getConnection();
	
	public int createOrder(Orders order){
		String sql="INSERT INTO orders (orderDate, arrivalTime, departureTime, comment, userId, hotelRoomId, status) VALUES (?,?,?,?,?,?,?)";
		PreparedStatement stmt =null;
		ResultSet keys=null;
		int orderId=-1;
		
		try{
			stmt=conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS );
			
			stmt.setString(1, order.getOrderDate());
			stmt.setString(2, order.getArrivalTime());
			stmt.setString(3, order.getDepartureTime());
			stmt.setString(4, order.getComment());
			stmt.setInt(5, order.getUserId());
			stmt.setInt(6, order.getHotelRoomId());
			stmt.setString(7, order.getStatus());
			
			int affected=stmt.executeUpdate();
			if(affected==1){
				keys =stmt.getGeneratedKeys();
				keys.next();
				int newKey=keys.getInt(1);
				order.setOrderId(newKey);
			}
			orderId=order.getOrderId();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(keys !=null){
				try {
					keys.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		ConnectionManager.releaseConnection(conn);
		return orderId;
	}
	
	public List<Orders> getListOrders(String orderStatus){
		String sql ="SELECT orderId, orderDate, arrivalTime, departureTime, comment, status, userId, hotelRoomId "+
				"FROM orders WHERE status='"+orderStatus+"'";
		List<Orders> result =null;
		Statement stmt=null;
		ResultSet rs=null;
		
		try{
			stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs=stmt.executeQuery(sql);
			result =new ArrayList<Orders>();
			
			while(rs.next()){
				Orders order =new Orders();
				order.setArrivalTime(rs.getString("arrivalTime"));
				order.setComment(rs.getString("comment"));
				order.setDepartureTime(rs.getString("departureTime"));
				order.setHotelRoomId(rs.getInt("hotelRoomId"));
				order.setOrderDate(rs.getString("orderDate"));
				order.setOrderId(rs.getInt("orderId"));
				order.setStatus(rs.getString("status"));
				order.setUserId(rs.getInt("userId"));
				result.add(order);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		ConnectionManager.releaseConnection(conn);
		return result;
	}
	
	public List<Map<String, Object>> getListAdminOrder(String orderStatus){
		String sql="{call getOrdersByStatus(?)}";
		
		List<Map<String, Object>> list =null;
		ResultSet rs=null;
		CallableStatement clstmt=null;
		try{
			clstmt =conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			clstmt.setString(1, orderStatus);
			rs=clstmt.executeQuery();
			list =new ArrayList<Map<String, Object>>();
			
			while(rs.next()){
				Map<String, Object> ordersDetail=new HashMap<String, Object>();
				Orders order =new Orders();
				HotelRooms room =new HotelRooms();
				Contacts contact=new Contacts();
				
				order.setOrderDate(rs.getString("orderDate"));
				order.setArrivalTime(rs.getString("arrivalTime"));
				order.setDepartureTime(rs.getString("departureTime"));
				order.setOrderId(rs.getInt("orderId"));
				order.setUserId(rs.getInt("userId"));
				room.setHotelRoomId(rs.getInt("hotelRoomId"));
				room.setArticle(rs.getString("article"));
				contact.setFirstName(rs.getString("firstName"));
				contact.setLastName(rs.getString("lastName"));
				contact.setPhone(rs.getString("phone"));
				
				ordersDetail.put("order", order);
				ordersDetail.put("room", room);
				ordersDetail.put("contact", contact);
				list.add(ordersDetail);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(clstmt!=null){
				try {
					clstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		ConnectionManager.releaseConnection(conn);
		return  list;
	}
	
	public Orders getCurrentOrderByID(int orderId){
		String sql="SELECT orderId, orderDate, arrivalTime, departureTime, comment, status, userId, hotelRoomId"+
				" FROM orders WHERE orderId=?";
		PreparedStatement stmt=null;
		ResultSet rs=null;
		Orders order =null;
		try{
			stmt=conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			stmt.setInt(1, orderId);
			rs =stmt.executeQuery();
			
			while(rs.next()){
				order =new Orders();
				order.setArrivalTime(rs.getString("arrivalTime"));
				order.setComment(rs.getString("comment"));
				order.setDepartureTime(rs.getString("departureTime"));
				order.setHotelRoomId(rs.getInt("hotelRoomId"));
				order.setOrderDate(rs.getString("orderDate"));
				order.setOrderId(rs.getInt("orderId"));
				order.setStatus(rs.getString("status"));
				order.setUserId(rs.getInt("userId"));
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		ConnectionManager.releaseConnection(conn);
		return order;
	}
}
